package org.streets.eis.ext.analysis.internal.buildlogic;

import java.util.List;
import org.slf4j.Logger;
import org.streets.commons.util.StringUtils;
import org.streets.database.SQLConnection;
import org.streets.database.datadict.DataDict;
import org.streets.database.datadict.TableRelations;
import org.streets.eis.ext.analysis.entities.DisplayField;
import org.streets.eis.ext.analysis.entities.OrderField;
import org.streets.eis.ext.analysis.entities.QueryStruct;
import org.streets.eis.ext.analysis.entities.WhereField;
import org.streets.eis.ext.analysis.internal.SqlBuilder;

public class SqlBuilderImpl implements SqlBuilder {

	private static final long serialVersionUID = 1L;
	protected DataDict _dict;
	private TableRelations _relations;
	private Logger _logger;
	protected final SQLConnection _connection;
	
	public SqlBuilderImpl(Logger logger, DataDict dict, TableRelations relations,SQLConnection connection) {
		this._dict = dict;
		this._relations = relations;
		this._logger = logger;
		this._connection = connection;
	}
	
	private String buildFieldExpr(List<WhereField> whereFields, String condition){

		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < whereFields.size(); i++){
			sb.append(whereFields.get(i).getAlias() + " "+condition+" ");
		}
		if (sb.length() > condition.length()) {
		  sb.setLength(sb.length() - condition.length() - 1);
		}
		
		return sb.toString();
	}
	
	private String buildFromClause(QueryStruct struct) {
		SqlBuilderArithmetic arithmetic = new SqlBuilderArithmetic(_logger, _dict, _relations,_connection);
		return arithmetic.buildFromClause(struct);
	}	
	
private String buildFullFieldName(DisplayField field){
	String fullFieldName;
	if ((field.getFuncSign()!=null)&&(field.getFuncSign() != "")){
		fullFieldName = field.getFuncSign() +"("+ field.getFieldCode() + "." + field.getFieldCode()+")";
	}else{
		fullFieldName = field.getTableCode() + "." + field.getFieldCode();
	}
	return fullFieldName;
}

	
	private String buildOrderClause(QueryStruct query) {
		List<OrderField> fields = query.getOrderFields();
		StringBuilder sb = new StringBuilder();
		
		if (fields.size() > 0) {
			for (OrderField f : fields){//注意排好序orderIndex
				sb.append(f.getTableCode() + "." + f.getFieldCode() +" "+ f.getOrderType() );
				sb.append("," );
			}
			
			sb.setLength(sb.length()-1);
		}
		
		return sb.toString();
	}

	
	private String buildSelectClause(QueryStruct query,StringBuilder fromClause) {
		List<DisplayField> fields = query.getDisplayFields();

		StringBuilder sb = new StringBuilder();
		sb.append("select ");
		int k = 1;
		for (DisplayField field : fields){ 
			String fullFieldName = buildFullFieldName(field);
			
			if (field.isCodeField()){//是代码
				String subAlias = "t" + k;//子查询别名
				String subQry = buildSubQry(field.getAttr(),subAlias);//子查询
				
				//添加到from子句
				fromClause.append(" Join " + subQry);
				//TODO:表中存放的是id还是code，需要统一确定
				fromClause.append(" on " + fullFieldName + "=" + subAlias + ".code");
				
				//sb.append(fullFieldName + ",");//这个是代码，可以不显示【如果要显示，必须修改组装模板数据VM的代码】
				sb.append(subAlias + ".name");
				k++;
			}
			else{//不是代码
				sb.append(fullFieldName);
			}
			
			sb.append("," );
		}//for
		
		sb.setLength(sb.length()-1);
		return sb.toString();
	}
	
 
	private String buildSubQry(String codeGroup,String alias){
			
			String s =" (select id,code,name,alias from EIS_CODE_ITEM where group_id='"+codeGroup+"') " + alias;
			
			return s;
		}
	
	private String buildWhere(WhereField field){
		// TODO:界面提交时要根据数据类型作验证，以保证这里的正确
		String v = "";
		//if (field.isCodeField()){//如果是代码字段
		v = field.getValue();
			//}else{
			//v = field.getDispValue();//[可以赋给value，然后取value的值]
			//}

		String s ;
		if ((field.getOperSign().equalsIgnoreCase("%..")) || (field.getOperSign().equalsIgnoreCase("..%"))
			|| (field.getOperSign().equalsIgnoreCase("%..%"))){ 
		    return s = field.getTableCode()+"."+field.getFieldCode() + " like '" + field.getOperSign().replaceAll("\\.\\.",v) + "' ";
		}
		
		if ((field.getOperSign().equalsIgnoreCase("IS NULL")) 
			|| (field.getOperSign().equalsIgnoreCase("IS NOT NULL"))){ 
			return s = field.getTableCode()+"."+field.getFieldCode() + " " + field.getOperSign() + " ";
		}

		s = field.getTableCode() + "." + field.getFieldCode() + field.getOperSign();
		
		if (field.getDataType().equalsIgnoreCase("string")){
		   return s +"'" + v +"'";
		}else if (field.getDataType().equalsIgnoreCase("integer")){
			return s + v;
		}else if (field.getDataType().equalsIgnoreCase("double")){
			return s + v;
		}else if (field.getDataType().equalsIgnoreCase("date")){
			return s + "to_date('" + v + "','yyyy-mm-dd')";
		}else if (field.getDataType().equalsIgnoreCase("datetime")){
			return s + "to_date('" + v + "','yyyy-mm-dd hh24:mi:ss')";
		}else if (field.getDataType().equalsIgnoreCase("time")){
			//注意这里,数据库中的是Date，带有日期; eg:to_char(timefield,'hh24:mi:ss') > '08:00:00'
			return s = " to_char(" + field.getTableCode() + "." + field.getFieldCode() + ",'hh24:mi:ss')"
			  			+ field.getOperSign()
			  			+ "'" +  v + "'";	
		}else{
			return s + v;
		}
		
	}
	
	private String buildWhereClause(QueryStruct query) {
		List<WhereField> fields = query.getWhereFields();
		String expr = query.getConditionExpr();
		
		if (StringUtils.isEmpty(expr)){
			expr = buildFieldExpr(fields, "and");
			query.setConditionExpr(expr);
		}		
		
		String s = replaceWhereExpr(fields, expr);		
		return s;
	}

	
	private String replaceWhereExpr(List<WhereField> wherefields,String whereExpr){
		String s = whereExpr; 
		for (int i=0; i< wherefields.size() ; i++){
			String alias = wherefields.get(i).getAlias();//别名
			String where = buildWhere(wherefields.get(i));
		    s = s.replaceAll(alias, where);
		}
		return s;
	}
	
	public String build(QueryStruct struct) {

		String where = buildWhereClause(struct);
		String order = buildOrderClause(struct);
		
		String s2 = buildFromClause(struct);

		//处理显示字段中的代码字段
		StringBuilder from = new StringBuilder();
		from.append(s2);
		
		String select = buildSelectClause(struct,from);
		
		//return select + " " + from.toString() +" " + where + " " + order;
		return null;

	}
	
	 
   
	 

	 
	
	 
 
	 
	 
}
